<?php

class m_penyusutan extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    // get last inserted id
    function get_last_inserted_id() {
        return $this->db->insert_id();
    }
    
    function get_penyusutan_by_barang_id_penyusutan_tgl_awal_akhir($params) {
        $sql = "SELECT * FROM penyusutan_detail WHERE barang_id = ? AND penyusutandet_tgl_awal = ? AND penyusutandet_tgl_akhir = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_last_penyusutan_id() {
        $sql = "SELECT penyusutan_id FROM penyusutan ORDER BY penyusutan_id DESC LIMIT 0,1";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['penyusutan_id'];
        } else {
            return array();
        }
    }

    function get_count_all_penyusutan() {
        $sql = "SELECT COUNT(*)'total' FROM penyusutan";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }
  // get user profil
    function get_all_penyusutan_pagination($params) {
        $sql = "SELECT a.* FROM penyusutan a
                ORDER BY a.penyusutan_id DESC
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    // insert
    function insert($params) { 
        // update by this date
        $sql = "INSERT INTO penyusutan(penyusutan_tgl, penyusutan_keterangan, penyusutan_status, mdb, mdd) 
                VALUES (?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    function update_status($params) { 
        $sql = "UPDATE penyusutan
                SET penyusutan_status = ?,
                mdd = NOW()
                WHERE penyusutan_id = ?";
        return $this->db->query($sql, $params);
    }
    
    // hitung tanggal berlaku
    function hitung_tanggal_penyusutan($params) { 
        $sql = "SELECT LAST_DAY(?)'tanggal'"; 
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['tanggal'];
        } else {
            return '';
        }
    }
    
    function hitung_jumlah_bulan_penyusutan($params) { 
        $sql = "select TIMESTAMPDIFF(month, ?, ?)'total'"; 
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    } 
    
    function get_tgl_penyusutan_by_bulan($params) { 
        $sql = "SELECT DATE_ADD(?, INTERVAL ? MONTH);"; 
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['tanggal'];
        } else {
            return '';
        }
    }
    
    function get_tahun_penyusutan() {
        $sql = "SELECT DISTINCT YEAR(penyusutan_tgl)'tahun' FROM penyusutan";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    function get_barang_penyusutan($params) { 
        $sql = "SELECT *, ROUND((barang_harga_beli/(barang_umurekonomis)),0)'pertahun', ROUND((barang_harga_beli/(barang_umurekonomis*12)),0)'perbulan' FROM barang
                WHERE barang_kondisi = 'baik' 
                AND (barang_tglberakhir > NOW() OR barang_tglberakhir > barang_tglpenyusutan OR barang_tglpenyusutan IS NULL)
                AND barang_susut = '1' AND barang_tglbeli <= ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
     }
     
     function get_barang_penyusutan_by_penyusutan_id($params) { 
        $sql = "SELECT c.barang_nama, c.barang_noinventaris, d.kategoribarang_name, b.*
                FROM penyusutan a JOIN penyusutan_detail b ON a.penyusutan_id=b.penyusutan_id
                JOIN barang c ON c.barang_id=b.barang_id
                JOIN kategoribarang d ON d.kategoribarang_cd = c.kategoribarang_cd
                WHERE a.penyusutan_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
     }
     
     function get_next_tgl_penyusutan($params) { 
         $sql = "SELECT DATE_ADD(?, INTERVAL ? MONTH)'tanggal'";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['tanggal'];
        } else {
            return '';
        }
     }
     
     function insert_detail_penyusutan($params) { 
        // update by this date
        $sql = "INSERT INTO penyusutan_detail(penyusutan_id, barang_id, penyusutandet_tgl_awal, penyusutandet_tgl_akhir, penyusutandet_jmlbulan, penyusutandet_perbulan, penyusutandet_nilai_awal, penyusutandet_nilai_akhir, mdb, mdd)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }
    
    function get_by_id($params) { 
         $sql = "SELECT * FROM penyusutan WHERE penyusutan_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
     }
     
     function get_max_tgl_penyusutan() { 
        $sql = "SELECT MAX(penyusutan_tgl)'tanggal' FROM penyusutan"; 
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['tanggal'];
        } else {
            return '';
        }
    }
    
    function get_penyusutan_masih_proses() { 
        $sql = "SELECT * FROM penyusutan WHERE penyusutan_status = '0'"; 
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    function update_penyusutan_barang($params) { 
        // update by this date
        $sql = "UPDATE barang
                SET barang_nilai_buku = ?,
                barang_tglpenyusutan = ? 
                WHERE barang_id = ?";
        return $this->db->query($sql, $params);
    }
}
